![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Part VII
|
Appendix | A | Review of Tuning Guidelines |
B | Quick Reference | |
C | Flowcharts | |
D | Glossary | |
E | Oracle Tuning Parameters | |
F | Contents of the CD-ROM |
This appendix is intended as a quick reference to many of the topics presented throughout this book.
The following sections review many of the concepts presented in Part II, Tuning the Server. They review some of the areas of importance and how to find and overcome performance bottlenecks. The following sections relate primarily to the tuning parameters for the OS and Oracle.
The System Global Area (SGA) contains the shared pool, the redo log buffer, and the database block buffers.
Shared Pool
The shared pool contains the library cache, the data dictionary cache, and the shared session area (with the multithreaded server).
Library Cache
The library cache contains the shared SQL and PL/SQL areas. You can improve performance by both increasing the cache-hit rate in the library cache and by speeding access to the library cache by holding infrequently used SQL statements in cache longer.
The V$LIBRARYCACHE table contains statistics about how well you are using the library cache. The important columns to view in this table are PINS and RELOADS:
A low number of reloads relative to the number of executions indicates a high cache-hit rate.
Data Dictionary Cache
The data dictionary cache contains a set of tables and views that Oracle uses as a reference to the database. It is here that Oracle stores information about both the logical and physical structure of the database.
To check the efficiency of the data dictionary cache, check the cache-hit rate. Statistics for the data dictionary cache are stored in the dynamic performance table V$ROWCACHE (the data dictionary cache is sometimes known as the row cache). The important columns to view in this table are GETS and GETMISSES:
A low number of cache misses are expected, especially during startup, when the cache has not been populated.
Shared Session Information
In a multithreaded server configuration, the session information is also stored in the shared pool. This information includes the private SQL areas as well as sort areas. It is important to make sure that you do not run out of memory for this shared session information.
To determine whether you need to increase space for these shared sessions, you can extract the sum of memory allocated for all sessions and the maximum amount of memory allocated for sessions from the dynamic performance table V$SESSTAT. If the maximum amount of memory used is high, it may be necessary to increase the size of the shared pool. Because the shared pool is used for other functions as well (such as the library cache and the data dictionary cache), it is a good idea to increase the size of the shared pool to accommodate the additional memory usage. If you have enough memory in your system, increase the shared pool by the maximum amount of memory used by the shared server processes. If you have a limited amount of memory, use the sum of memory allocated to sessions (obtained when an average number of users were connected and running) as the basis for the amount of memory for the shared pool.
Database Block Buffer Cache
Probably the most important Oracle cache in the system is the buffer cache. The buffer cache makes up the majority of the Oracle SGA and is used for every query and update in the system.
The statistics for the buffer cache are kept in the dynamic performance table V$SYSSTAT. The important columns to view in this table are listed here:
The sum of the values in DB BLOCK GETS and CONSISTENT GETS represents the total number of requests for data.
The cache-hit ratio is determined using this formula:
Cache Hit Ratio = 1 - ( PHYSICAL READS / ( DB BLOCK GETS + CONSISTENT GETS))
The block buffers are the most important area of the SGA and must be tuned because of the large effect they have on the system and the amount of resources they consume.
I prefer to separate the performance-enhancement options from the general tuning of Oracle. Performance enhancements tend to be things that may or may not help your configuration and application; in fact, they may sometimes hurt if youre not careful. On the other hand, tuning parameters always helps, based on correct interpretation of Oracle statistics. The following sections review a few of the enhancements you have seen throughout the book.
Block Size
Depending on your configuration and data access patterns, you may be able to benefit from using a larger block size. With a larger block size, under certain circumstances, you get the benefit of less wasted space and more efficient I/O. Here are a few guidelines that may help you decide whether changing the size of DB_BLOCK_SIZE can benefit you:
Because changing the block size unnecessarily causes increased I/O overhead, this change does carry some risk. Change the block size with caution.
Clusters
A cluster, sometimes called an index cluster, is an optional method of storing tables in an Oracle database. Within a cluster, multiple related tables are stored together to improve access time to the related items. Clusters are useful in cases where related data is often accessed together. The existence of a cluster is transparent to users and to applications; the cluster affects only how data is stored.
A cluster can be useful for tables in which data is primarily accessed together in a join. In such situations, the reduced I/O needed to bring the additional data into the SGA and the fact that the data is already cached can be a big advantage.
However, for situations in which the tables have a large number of INSERT statements or the data is not frequently accessed together, a cluster is not useful and should not be used.
Do not cluster tables if full-table scans are often performed on only one of the tables in the cluster. The additional space required by the cluster and the additional I/O reduces performance.
Reduce Fragmentation
Fragmentation is the condition that occurs when pieces of the database are no longer contiguous. Fragmentation can consist of disk fragmentation or tablespace fragmentation. Both of these types of fragmentation usually affect performance.
Disk fragmentation usually causes multiple I/Os to occur when one I/O would have been sufficient (for example, with chained or migrated rows). Disk fragmentation can also be caused when the extents that make up the database segments are noncontiguous, which can happen with excessive dynamic growth.
Tablespace fragmentation is caused by the dropping and creation of segments. This can create large free areas between segments, which result in the inefficient use of space and excessive disk seeks over the empty areas. Tablespace fragmentation can also prevent Oracle from taking advantage of multiblock reads.
One way to eliminate fragmentation is to export the table or tablespace data, remove and re-create the table or tablespace, and import the data. By eliminating fragmentation, you can reduce excessive I/Os and CPU usage, streamlining data access. Any overhead and unnecessary I/Os you can reduce will improve system performance.
Hash Clusters
A hash cluster is similar to a cluster except that it uses a hash function rather than an index to reference the cluster key. A hash cluster stores the data based on the result of a hash function. The hash function is a numeric function that determines the data block in the cluster based on the value of the cluster key.
To achieve good performance from a hash cluster, you must meet the following criteria:
If you can take advantage of hashing by meeting these strict criteria, you will see very good performance. Hashing is extremely efficient under the right conditions; however, having a hash cluster under the wrong conditions can cause some performance degradation.
Indexes
An index, like the index in this book, is an optional structure designed to help you achieve faster access to your data. When optimally configured and used, indexes can significantly reduce I/O to the data files and greatly improve performance. You must first decide whether an index is appropriate for the data and access patterns in your particular system. Having decided to use an index, you must decide which columns to index. Indexing appropriately can greatly improve performance by reducing I/Os and speeding access times.
Careful planning and periodic testing with SQL Trace can lead to very effective use of indexes, with optimal performance being the outcome.
Multiblock Reads
When performing table scans, Oracle has the capability to read more than one block at a time, thus speeding I/Os. By reading more than one block at a time, a larger chunk of data can be read from the disk, eliminating some disk seeks. By reducing disk seeks and reading larger blocks, both I/O and CPU overhead are reduced.
The amount of data read in a multiblock read is specified by the Oracle initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. The value for this parameter should always be set high because there is rarely any disadvantage in doing so. The size of the individual I/O requests depends on both DB_FILE_MULTIBLOCK_READ_COUNT and DB_BLOCK_SIZE. A good value for multiblock reads is 64K.
Multiblock Writes
Multiblock writes are similar in nature to multiblock reads and have many of the same requirements. The multiblock write feature is new with Oracle version 7.3. Under certain conditions, you can now perform multiblock writes.
Multiblock writes are available through the direct path loader as well as through sorts and index creations. As with multiblock reads, multiblock writes reduce I/O and CPU overhead by writing multiple database blocks in one larger I/O operation.
The amount of data written in a multiblock write is specified by the Oracle initialization parameter DB_FILE_MULTIBLOCK_WRITE_COUNT. The size of the individual I/O requests depends on both DB_FILE_MULTIBLOCK_WRITE_COUNT and DB_BLOCK_SIZE. As with multiblock reads, a good value is 64K.
Oracle Parallel Query Option
The Oracle Parallel Query option makes it possible for some Oracle functions to be processed by multiple server processes. The functions affected are queries, index creation, data loading, and recovery. For each of these functions, the general principle is the same: Keep the processing going while Oracle is waiting for I/O.
For most queries, the time spent waiting for the data to be retrieved from disk usually overshadows the amount of time actually spent processing the results. With the Parallel Query option, you can compensate for this wasted time by using several server processes to execute the query. While one process is waiting for I/Os to complete, other processes can execute. If you are running on a Symmetric Multiprocessor (SMP) computer, a cluster, or a Massively Parallel Processing (MPP) machine, you can take maximum advantage of the Parallel Query option.
The amount of parallelism can be tuned with several of the Oracle initialization parameters:
Parameter | Description |
---|---|
PARALLEL_DEFAULT_MAX_SCANS | Specifies the maximum number of query servers to used by default for a query. This valued is used only if no value is specified in a PARALLEL hint or in the PARALLEL definition clause. This parameter limits the number of query servers used by default when the value of PARALLEL_DEFAULT_SCANSIZE is used by the query coordinator. |
PARALLEL_DEFAULT_SCANSIZE | Specifies the number of query servers to be used for a particular table. The size of the table divided by PARALLEL_DEFAULT_SCANSIZE determines the number of query servers, up to PARALLEL_DEFAULT_MAX_SCANS. |
PARALLEL_MAX_SERVERS | Maximum number of query servers or parallel recovery processes available for this instance. |
RECOVERY_PARALLELISM | The number of processes to be used for instance or media recovery. A large value can greatly reduce instance recovery time. A value of zero or 1 indicates that parallel recovery will not be done and that recovery will be serial. A good value for this parameter is in the range of the number of disks you have (up to 50). |
I am a real fan of the Parallel Query option. I have seen great improvements from the use of parallel queries as well as dramatic reductions in recovery time when the parallel recovery feature is used.
Oracle Parallel Server Option
The Oracle Parallel Server option is one of the most innovative and impressive options available from Oracle. With the Parallel Server option, you can cluster several computers together using a shared-disk subsystem and have multiple Oracle instances access the same database. If your application is suitable, you can see very good scalability from adding additional computers.
The Oracle Parallel Server option uses a sophisticated locking mechanism in conjunction with a shared-disk subsystem to allow multiple instances to access the same data. If you have an application that can take advantage of the Oracle Parallel Server architecture, you should see some very good performance improvements.
The two areas that can most influence the performance of your parallel server system are data partitioning and PCM lock management. Both of these can make a huge difference in the performance of your system.
TIP: By taking advantage of read-only tablespaces when applicable, you can reduce the number of PCM locks in your system. Because read-only tablespaces do not allow updates, no locking is necessary.
Spin Counts
Multiprocessor environments may benefit by tuning the parameter SPIN_COUNT. Under normal circumstances, if a latch is not available, the process sleeps for a while and then wakes up to try the latch again. If you are on a multiprocessor system, it is likely that the process holding the latch is currently processing on another CPU and will be finished in a short time. By setting SPIN_COUNT to a value greater than zero, the process spins while counting down from SPIN_COUNT to zero. If the latch is still not available, the process goes to sleep.
Setting SPIN_COUNT can hurt performance if youre not careful. This parameter should be set only for multiprocessor computers and should be monitored for effectiveness. A good value to try is 2000. The value of SPIN COUNT specifies how many times the process will spin before putting itself to sleep. Because the speed of processors varies, the time it takes to spin also varies, but the speed of the other process holding the desired resource also vary with the speed of the processor.
OS tuning is very specific and dependent on the OS you are running. There are, however, some general guidelines you can follow.
The general goal in tuning the server operating system is to simply provide the resources Oracle needs to function optimally. Heres a list of several areas that need attention:
OS Area | Comments |
---|---|
Memory | Enough memory should be allocated to Oracle to hold the entire SGA in physical memory. If your SGA pages out, performance is severely degraded. Paging out of user processes should also be avoided. |
I/O | The I/O subsystem should be tuned to have the least amount of overhead possible. By reducing overhead, the performance of the entire system is increased. |
Processes | The system should be configured to handle the required number of processes necessary to support your users. |
These and other OS features should be used with the goal of reducing system overhead. Any extra CPU time spent in the OS is CPU time not spent processing Oracle.
You should make use of any OS feature that can reduce overhead. Although these features vary from platform to platform and are OS specific, some common areas are listed here:
Feature | Comment |
---|---|
Post-wait semaphore | This feature is available on some platforms and reduces some of the overhead associated with traditional semaphores and scheduling. |
Scheduling parameters | Some OSes allow you to adjust scheduling to reduce the amount of preemption in the system. If available, use this feature. |
Cache affinity | Cache affinity is available in some operating systems and should be used with caution. Although some application such as decision support can benefit, OLTP may suffer slightly from the effects of cache affinity. |
Asynchronous I/O (AIO) | Most operating systems have AIO available; you should use it. The use of AIO reduces I/O processing overhead. |
All these OS features were designed to reduce excess overhead that takes away from user processing. By optimizing your OS to reduce overhead, you can enhance total system throughput.
The I/O system should be designed and implemented with the following goals in mind:
By following these guidelines and planning your system so that your disk drives can support the amount of disk I/O demanded of them, I/O should not be a problem.
Part III, Configuring the System, looked at several types of systems and determined the data access patterns, the system load, and optional features that can enhance performance. The systems that were reviewed are listed here:
System | Description |
---|---|
OLTP | Characteristics include many users, high I/O rates, much random I/O, and response-time constraints. The Oracle Parallel Server option is possibly a win for OLTP systems. |
Batch | Characteristics include system load-time constraints, index build-time constraints, and long-running queries. The Oracle Parallel Query option can benefit the typical batch system. Large block sizes also help. |
DSS | Characteristics include system load-time constraints, index build-time constraints, and long-running queries. The Oracle Parallel Query option definitely benefits the typical DSS system. Large block sizes and multiblock reads also help. The Oracle Parallel Server option may also benefit the DSS system. |
Data warehouse | The characteristics of a data warehouse system are much like those of a super DSS system: a huge amount of data, large queries, and heavy I/O usage. Both the Parallel Query and Parallel Server options may benefit the data warehouse system. |
BLOBs | BLOBs require significant I/O and response time. Data-feed interruption cannot be tolerated. Large block sizes are a must. |
Parallel server | The Oracle parallel server system can be enhanced by carefully partitioning tasks and allocating PCM locks efficiently. Because the parallel server system involves two or more systems working together, balancing transactions and functions is crucial. |
Optimal backup | If backup and recovery time is critical, there are several ways your system can be configured to optimize for these tasks. |
Miscellaneous | Other Oracle products (Oracle Financials, Oracle WebSystem, and others) have their own unique characteristics and types of enhancements. |
By looking at various types of systems and examining their characteristics, Part III, Configuring the System, gave you a feel for how these systems operate. By understanding the systems, you have a better idea of how to tune them for optimal performance.
Part IV, Tuning SQL, looked at tuning the application, which mainly involves tuning the SQL statements themselves. Several chapters explained how to tune specific SQL statements and how to use Oracle features to your advantage. Here is a list of some of the important topics discussed in Part IV:
By now, you should have an idea about the importance of tuning your SQL statements. You should also know how to take advantage of various Oracle features to improve the performance of those statements.
Part V, Tuning the Client, looked at the client computer itself. You learned about some of the things that can affect client performance and how to solve those problems. You also looked at how to tune the client system and how to take advantage of some popular graphical development tools. You saw how to use these tools to improve the performance of the SQL statements generated by those tools. Here is a list of some of the key topics in Part V:
You should now understand what affects the performance of the client machine and how to solve those problems. Part V of this book also introduced middleware products and how they can be used to improve the overall performance of your configuration.
Part VI, Tuning the Network, looked at the capacity of the network and how you can tune it. The real way to tune the physical network is to stay within its capacity. As your needs grow, you must improve the capacity of your network.
Previous | Table of Contents | Next |
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement. |